<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
Class Mreport extends MY_Model
{
    public function __construct()
    {
        parent::__construct();
    }
    
    /**
     * Lấy top các thiết bị(mạng) 
     * 
     * @param int $type     kiểu top     1: top mạng sử dụng nhiều băng thông nhất,2:top mạng nhiều người sử dụng nhất
     * @param int $number   số bản ghi cần lấy
     * @param int $offset   vị trí bản ghi bắt đầu
     * @return array  
     */
    public function getTopDevice($type,$time,$group,$number = 5,$offset = 0){

        //String limit
        $limit = $offset ? intval($offset) : '0';
        $stringLimit = " LIMIT " . $limit . "," . $number . " ";
        
        //String order
        if($type == 1) $stringOrder=' ORDER BY total DESC ';
        if($type == 2) $stringOrder=' ORDER BY client DESC ';
        
        //convert $time to timestamp
        $time=$this->convertTimestamp($time);  //trả về thời gian bắt đầu và kết thúc timestapm
        $startTime=$time[0];
        $endTime=$time[1];
        
        //sql group
        if($group != -1)$sqlGroup = " AND  gr.id='$group' ";     // không phải nhóm tất cả thì lọc theo group.
        else $sqlGroup = ' ';                                   //-1 là chọn tất cả,ko lọc group nữa
                
        
        //Lấy các thiết bị (mạng) sử dụng nhiều băng thông nhất
        $query="SELECT dv.id,dv.namedevice as name_device,ad.`name` as store,
                CONCAT(ad.address,',',dt.`name`,',',pr.`name`) as address ,
                SUM(ra.acctinputoctets) as total_down,SUM(ra.acctoutputoctets) as total_up ,
                SUM(ra.acctinputoctets + ra.acctoutputoctets) as total, COUNT(DISTINCT ra.callingstationid) as client,
                COUNT(DISTINCT ra.callingstationid)/(select count(DISTINCT callingstationid) from " . $this->radius . ".radacct)*100 as pct 
                FROM " . $this->cpanel . ".tbl_device as dv 
                INNER JOIN " . $this->radius . ".radcheck as rc ON dv.id = rc.id_device 
                INNER JOIN " . $this->radius . ".radacct as ra ON rc.username = ra.username 
                INNER JOIN " . $this->cpanel . ".tbl_address AS ad ON dv.id_address=ad.id 
                INNER JOIN " . $this->cpanel . ".district AS dt ON ad.id_district=dt.districtid 
                INNER JOIN " . $this->cpanel . ".province AS pr ON dt.provinceid=pr.provinceid 
                INNER JOIN " . $this->cpanel . ".tbl_group AS gr ON gr.id=dv.network_group 
                WHERE 
                UNIX_TIMESTAMP(ra.acctstarttime) >= '$startTime' AND UNIX_TIMESTAMP(ra.acctstarttime) <= '$endTime' 
               ".$sqlGroup."
                GROUP BY dv.id 
               ";
        $sqlDevices=$this->query($query.$stringOrder.$stringLimit);  
//        echo "<pre>";var_dump($sqlDevices->num_rows());die;
        if($sqlDevices->num_rows()== 0) return $device = array(); 
        $device=$sqlDevices->result_array();
        //convert bandwidth 
        foreach ($device as $key => $value)
        {    
            $device[$key]['total_down']=$this->formatSize($value['total_down']);
            $device[$key]['total_up']=$this->formatSize($value['total_up']);
            $device[$key]['total']=$this->formatSize($value['total']);
        }
        return $device;
    }
    
    /**
     * Lấy tổng băng thông theo nhóm mạng hoặc device
     * 
     * @return int tổng băng thông.
     */
    public function getTotalBandwidth($time,$group = null,$deviceId = null)
    {
        //convert $time to timestamp
        $time=$this->convertTimestamp($time);  //trả về thời gian bắt đầu và kết thúc timestapm
        $startTime=$time[0];
        $endTime=$time[1];
        
        //sql group
        if($group != -1 && $group != null )$sqlGroup = " AND  gr.id='$group' ";     // không phải nhóm tất cả và ko phải đang thống kê device thì lọc theo group.
        else $sqlGroup = ' ';     //còn lại thống kê tất cả hoặc theo device thì ko cần sql group
        
        //Query lấy tổng băng thông
        $query="SELECT SUM(ra.acctinputoctets + ra.acctoutputoctets) as total,
                SUM(ra.acctinputoctets) as total_down,SUM(ra.acctoutputoctets) as total_up ,COUNT(DISTINCT ra.callingstationid) as client
                FROM " . $this->cpanel . ".tbl_device as dv 
                INNER JOIN " . $this->radius . ".radcheck as rc ON dv.id = rc.id_device 
                INNER JOIN " . $this->radius . ".radacct as ra ON rc.username = ra.username 
                INNER JOIN " . $this->cpanel . ".tbl_group AS gr ON gr.id=dv.network_group 
                WHERE UNIX_TIMESTAMP(ra.acctstarttime) >= '$startTime' AND UNIX_TIMESTAMP(ra.acctstarttime) <= '$endTime' 
               ".$sqlGroup."
               ";
        if($deviceId != null)  // thống kê theo device
        $query.= " AND md5(dv.id) = '$deviceId' ";
        
        $totalBandwidth=$this->query($query)->result_array();
        $total=$this->formatSize($totalBandwidth[0]['total']);
        $totalUp=$this->formatSize($totalBandwidth[0]['total_up']);
        $totalDown=$this->formatSize($totalBandwidth[0]['total_down']);
        return array('total'=>$total,'totalUp'=>$totalUp,'totalDown'=>$totalDown,'client'=>$totalBandwidth[0]['client']);
    }
    
    
    
    /**
     * Lấy tổng số client vào hệ thống
     * 
     * @return int $totalClient tổng client 
     */
    public function getTotalClient()
    {
        $this->db_b = $this->load->database('radiusdb', TRUE);
        $totalClient=$this->db_b->query("select count(DISTINCT callingstationid) as total from ".$this->radius. ".radacct")->row()->total;
        return $totalClient;
    }

    /**
     * Lấy top client sử dụng nhiều băng thông
     * 
     * @param   string $time      thời gian tính
     * @param   int    $group     nhóm
     * @param   string $deviceId  thiết bị md5(id)
     * @param   int    $number    số lượng bản ghi lấy
     * @param   int    $offset    điểm bắt đầu lấy
     * @return  array
     */
    public function getTopClientNet($time,$group=null,$deviceId=null,$number = 10,$offset = 0)
    {
         //String limit
        $limit = $offset ? intval($offset) : '0';
        $stringLimit = " LIMIT " . $limit . "," . $number . " ";
        
        //convert $time to timestamp
        $time=$this->convertTimestamp($time);  //trả về thời gian bắt đầu và kết thúc timestapm
        $startTime=$time[0];
        $endTime=$time[1];
        
        //sql group
        if($group != -1 && $group != null )$sqlGroup = " AND  gr.id='$group' ";     // không phải nhóm tất cả thì lọc theo group.
        else $sqlGroup = ' ';
        
        //sql group
        if($group != -1 && $group != null )$sqlGroupo = " AND  gro.id='$group' ";     // không phải nhóm tất cả thì lọc theo group.
        else $sqlGroupo = ' ';
        
        if($deviceId != null)  // thống kê theo device
        {
            $sqlDevice= " AND md5(dv.id) = '$deviceId' ";
            $sqlDeviceo= " AND md5(dvo.id) = '$deviceId' ";
        }
        else 
        {    
            $sqlDevice = " "; 
            $sqlDeviceo = " "; 
        }
        //Lấy các thiết bị (mạng) sử dụng nhiều băng thông nhất
        $query="SELECT dv.id,ra.callingstationid as mac,cl.`name` as name_user,SUM(ra.acctinputoctets + ra.acctoutputoctets) as total,
                SUM(ra.acctinputoctets+ra.acctoutputoctets)/(
                                                                SELECT SUM(rao.acctinputoctets + rao.acctoutputoctets) FROM " . $this->radius . ".radacct as rao
                                                                INNER JOIN " . $this->radius . ".`radcheck` as rco ON rao.username=rco.username
                                                                INNER JOIN " . $this->cpanel . ".tbl_device as dvo ON rco.id_device=dvo.id
                                                                INNER JOIN " . $this->cpanel . ".tbl_group AS gro ON gro.id=dvo.network_group 
                                                                WHERE UNIX_TIMESTAMP(rao.acctstarttime) >= '$startTime' AND UNIX_TIMESTAMP(rao.acctstoptime) <= '$endTime' 
                                                                ".$sqlGroupo.$sqlDeviceo."
                                                            )*100 as pct
                FROM " . $this->cpanel . ".tbl_device as dv 
                INNER JOIN " . $this->radius . ".radcheck as rc ON dv.id = rc.id_device 
                INNER JOIN " . $this->radius . ".radacct as ra ON rc.username = ra.username 
                LEFT JOIN " . $this->cpanel . ".customer_log AS cl ON ra.callingstationid =cl.mac
                    INNER JOIN " . $this->cpanel . ".tbl_group AS gr ON gr.id=dv.network_group 
                    WHERE UNIX_TIMESTAMP(ra.acctstarttime) >= '$startTime' AND UNIX_TIMESTAMP(ra.acctstarttime) <= '$endTime' 
                    ".$sqlGroup.$sqlDevice. "
                GROUP by ra.callingstationid ORDER BY total DESC";          
//        var_dump($query);die;
        $sqlClient=$this->query($query.$stringLimit);  
        if($sqlClient->num_rows()== 0) return $client = array(); 
        $client=$sqlClient->result_array();
       
        //convert bandwidth 
        foreach ($client as $key => $value)
        {    
            $client[$key]['total']=$this->formatSize($value['total']);
        }
        return $client;
    }
    
    /**
     * Lấy top người dùng thường xuyên vào mạng nhất(đang lấy số lần)
     * 
     * @param type $number
     * @param type $offset
     * @return type
     */
     public function getTopClientAccess($time,$group = null,$deviceId = null,$number = 10,$offset = 0)
    {
         //String limit
        $limit = $offset ? intval($offset) : '0';
        $stringLimit = " LIMIT " . $limit . "," . $number . " ";
        
          //convert $time to timestamp
        $time=$this->convertTimestamp($time);  //trả về thời gian bắt đầu và kết thúc timestapm
        $startTime=$time[0];
        $endTime=$time[1];
        
        //sql group
        if($group != -1 && $group != null )$sqlGroup = " AND  gr.id='$group' ";     // không phải nhóm tất cả thì lọc theo group.
        else $sqlGroup = ' ';
        
        //sql group
        if($group != -1 && $group != null )$sqlGroupo = " AND  gro.id='$group' ";     // không phải nhóm tất cả thì lọc theo group.
        else $sqlGroupo = ' ';
       
        if($deviceId != null)  // thống kê theo device
        {
            $sqlDevice= " AND md5(dv.id) = '$deviceId' ";
            $sqlDeviceo= " AND md5(dvo.id) = '$deviceId' ";
        }
        else 
        {    
            $sqlDevice = " "; 
            $sqlDeviceo = " "; 
        }
//        //String order
//        if($type == 1) $stringOrder=' ORDER BY total DESC ';
//        if($type == 2) $stringOrder=' ORDER BY client DESC ';
        
        //Lấy các thiết bị (mạng) sử dụng nhiều băng thông nhất
        $query= "SELECT dv.id,count(ra.callingstationid) as number_time,ra.callingstationid as mac,cl.`name` as name_user,
                count(ra.callingstationid)/(
                                            SELECT count(radacctid) FROM " . $this->radius . ".`radacct` as rao
                                            INNER JOIN " . $this->radius . ".`radcheck` as rco ON rao.username=rco.username
                                            INNER JOIN " . $this->cpanel . ".tbl_device as dvo ON rco.id_device=dvo.id
                                            INNER JOIN " . $this->cpanel . ".tbl_group AS gro ON gro.id=dvo.network_group 
                                            WHERE UNIX_TIMESTAMP(rao.acctstarttime) >= '$startTime' AND UNIX_TIMESTAMP(rao.acctstoptime) <= '$endTime' 
                                            ".$sqlGroupo.$sqlDeviceo."
                                            )*100 as pct 
                FROM " . $this->radius . ".`radacct` as ra LEFT JOIN " . $this->cpanel . ".customer_log AS cl ON ra.callingstationid =cl.mac 
                    INNER JOIN " . $this->radius . ".`radcheck` as rc ON ra.username=rc.username
                    INNER JOIN " . $this->cpanel . ".tbl_device as dv ON rc.id_device=dv.id
                    INNER JOIN " . $this->cpanel . ".tbl_group AS gr ON gr.id=dv.network_group 
                    WHERE UNIX_TIMESTAMP(ra.acctstarttime) >= '$startTime' AND UNIX_TIMESTAMP(ra.acctstarttime) <= '$endTime' 
                    ".$sqlGroup.$sqlDevice."
                GROUP by callingstationid ORDER BY number_time DESC ";      
//        var_dump($query);die;
        $sqlClient=$this->query($query.$stringLimit);  
        if($sqlClient->num_rows()== 0) return $client = array(); 
        $client=$sqlClient->result_array();
//        var_dump($client);die;
        return $client;
    }
    
   /**
    * Lấy các group để tạo ra select group trên view
    * 
    * @param      array  $group   mảng group_id của user
    * @param      int    $level   phân quyền của user
    * @return     object 
    */
    public function getGroupNet($group,$level)
    {
        //Quản lí thiết bị đã được lọc trước khi gọi hàm này
        //$group != null thì đây là quản lí group
        if($group != null)
        {
            $group='( '.implode(',', $group).' )';
            $query=" WHERE id IN $group ";
        }
        else $query = ' ';     //đây là super admin            
        return $this->db->query("SELECT id,`name` FROM tbl_group " . $query)->result_object();        
    }
    
    /**
     * Tính số người dùng theo ngày
     * 
     * @param string    $time      thời gian tính
     * @param int       $group        nhóm tính
     * @return object
     */
    public function clientPerDay($time,$group = null,$deviceId = null)
    {
        $time= explode('-', $time);
        $startTime=date("Y/m/d",strtotime(str_replace('/', '-', $time[0])));  //phai doi d/m/y thanh d-m-y thi strtotime ms chay
        $stopTime=date("Y/m/d",strtotime(str_replace('/', '-', $time[1])));
        
        //sql group
        if($group != -1 && $group != null )$sqlGroup = " AND  gr.id='$group' ";     // không phải nhóm tất cả và ko phải đang thống kê device thì lọc theo group.
        else $sqlGroup = ' ';     //còn lại thống kê tất cả hoặc theo device thì ko cần sql group
        
        if($deviceId != null)  // thống kê theo device
        $sqlDevice= " AND md5(dv.id) = '$deviceId' ";
        else $sqlDevice = " "; 
        
        $query="    SELECT count(DISTINCT ra.callingstationid) as client, DATE_FORMAT(ra.acctstarttime,'%d/%m/%Y') as `date` 
                    FROM " . $this->radius . ".radacct as ra 
                    INNER JOIN " . $this->radius . ".radcheck as rc ON ra.username =  rc.username
                    INNER JOIN " . $this->cpanel . ".tbl_device as dv ON rc.id_device=dv.id
                    INNER JOIN " . $this->cpanel . ".tbl_group AS gr ON gr.id=dv.network_group
                    WHERE DATE_FORMAT(ra.acctstarttime,'%Y/%m/%d') >= '$startTime' AND DATE_FORMAT(ra.acctstarttime,'%Y/%m/%d') <= '$stopTime'
                    ".$sqlGroup.$sqlDevice."
                    GROUP BY DATE_FORMAT(acctstarttime,'%d/%m/%Y') ORDER BY ra.acctstarttime ASC ";
        
        $clientPerDay=$this->query($query)->result_object();
        return $clientPerDay;
        
    }
    
    /**
     * Tính lượng băng thông sử dụng theo ngày
     * 
     * @param string $time
     * @param int $group
     * @return object
     */
    public function bandwidthPerDay($time,$group = null ,$deviceId = null)
    {
        $time= explode('-', $time);
        $startTime=date("Y/m/d",strtotime(str_replace('/', '-', $time[0])));  //phai doi d/m/y thanh d-m-y thi strtotime ms chay
        $stopTime=date("Y/m/d",strtotime(str_replace('/', '-', $time[1])));
    
        //sql group
        if($group != -1 && $group != null )$sqlGroup = " AND  gr.id='$group' ";     // không phải nhóm tất cả thì lọc theo group.
        else $sqlGroup = ' ';
        
        if($deviceId != null)  // thống kê theo device
        $sqlDevice= " AND md5(dv.id) = '$deviceId' ";
        else $sqlDevice = " "; 
        
        $query="    SELECT SUM(ra.acctinputoctets+ra.acctoutputoctets) as total, DATE_FORMAT(ra.acctstarttime,'%d/%m/%Y') as `date` 
                    FROM " . $this->radius . ".radacct as ra 
                    INNER JOIN " . $this->radius . ".radcheck as rc ON ra.username =  rc.username
                    INNER JOIN " . $this->cpanel . ".tbl_device as dv ON rc.id_device=dv.id
                    INNER JOIN " . $this->cpanel . ".tbl_group AS gr ON gr.id=dv.network_group
                    WHERE DATE_FORMAT(ra.acctstarttime,'%Y/%m/%d') >= '$startTime' AND DATE_FORMAT(ra.acctstarttime,'%Y/%m/%d') <= '$stopTime'
                    ".$sqlGroup.$sqlDevice."
                    GROUP BY DATE_FORMAT(acctstarttime,'%d/%m/%Y') ORDER by ra.acctstarttime ASC";
        $bw=$this->query($query)->result_object();
        
//        foreach ($bw as $key => $value)
//        {
//            $value->total=$this->formatSize($value->total);
//        }
//        echo "<pre>";var_dump($bw);die;
        return $bw;
        
    }
    
    
    
    
    ////////////////             DEVICE REPORT                         ///////////////////////////////
    
    
    /**
     * Lấy danh sách các thiết bị
     * 
     * @param  int  $number  số bản ghi cần lấy
     * @param  int  $offset  vị trí đầu tiên để lấy
     * @return array
     */
    public function getDevice($number = 0, $offset = 0) {
        $limit = $offset ? intval($offset) : '0';

        $sql = "SELECT CONCAT(a.address,',',dt.`name`,',',p.`name`) as address,dt.`name` AS dname, 
                        p.`name` AS pname, 
                        d.nasid, 
                        d.active, 
                        from_unixtime(d.time_update, '%H:%i:%s %m/%d/%Y') AS lastonline,
                        d.id AS did, 
                        d.namedevice, 
                        SUM(rd.acctinputoctets + rd.acctoutputoctets) AS bw, COUNT(DISTINCT rd.callingstationid) as user
                FROM " . $this->cpanel .".tbl_device d INNER JOIN " . $this->cpanel .".tbl_address a ON d.id_address = a.id
                         INNER JOIN " . $this->cpanel . ".province p ON a.id_province = p.provinceid
                         INNER JOIN " . $this->cpanel . ".district dt ON a.id_district = dt.districtid
                         LEFT JOIN " . $this->radius . ".radcheck rc ON d.id = rc.id_device
                         LEFT JOIN " . $this->radius . ".radacct rd ON rc.username = rd.username "
                ;
        
        $user_id=$this->session->userdata('id');
        
        if ($this->session->userdata('level') == 2) { // đây là quản trị group
            $group=$this->_getGroup($user_id);
            $group='('.implode(',', $group).')';
            $sql .= " WHERE d.network_group IN $group ";
        }
        
        if($this->session->userdata('level') == 3){
            $sql .= "  INNER JOIN " . $this->cpanel .".manage_device_key as mdk ON d.id = mdk.device_id WHERE mdk.user_id = '$user_id'";
        }
        
        $sql .= " GROUP BY d.id ORDER BY d.namedevice ASC";
        $count = $this->query($sql)->num_rows();
        //echo $sql;
        $xdata = " LIMIT " . $limit . "," . $number . " ";

        $xs = $this->query($sql.$xdata)->result_object();
        
        return array('data' => $xs, 'count' => $count);
    }
    
    /**
     * Lấy top hệ điều hành sử dụng nhiều nhất
     * 
     * @param string $time   thời gian
     * @param int $group     nhóm mạng   
     * @param int $deviceId  id thiết bị (md5)
     * @return object
     */
    public function getTopOs($time,$group= null,$deviceId = null)
    {
        $time= explode('-', $time);
        $startTime=date("Y/m/d",strtotime(str_replace('/', '-', $time[0])));  //phai doi d/m/y thanh d-m-y thi strtotime ms chay
        $stopTime=date("Y/m/d",strtotime(str_replace('/', '-', $time[1])));
        
        //sql group
        if($group != -1 && $group != null )$sqlGroup = " AND  gr.id='$group' ";     // không phải nhóm tất cả thì lọc theo group.
        else $sqlGroup = ' ';
        
        //sql group
        if($group != -1 && $group != null )$sqlGroupo = " AND  gro.id='$group' ";     // không phải nhóm tất cả thì lọc theo group.
        else $sqlGroupo = ' ';
       
        if($deviceId != null)  // thống kê theo device
        {
            $sqlDevice= " AND md5(dv.id) = '$deviceId' ";
            $sqlDeviceo= " AND md5(dvo.id) = '$deviceId' ";
        }
        else 
        {    
            $sqlDevice = " "; 
            $sqlDeviceo = " "; 
        } 
      
        $query="    SELECT count(DISTINCT ra.callingstationid) as client,
                    IFNULL(cl.osname,'unknown') as osname  ,SUM(ra.acctinputoctets + ra.acctoutputoctets) as total,
                    SUM(ra.acctinputoctets+ra.acctoutputoctets)/(
                                                                SELECT SUM(rao.acctinputoctets + rao.acctoutputoctets) FROM " . $this->radius . ".radacct as rao
                                                                INNER JOIN " . $this->radius . ".`radcheck` as rco ON rao.username=rco.username
                                                                INNER JOIN " . $this->cpanel . ".tbl_device as dvo ON rco.id_device=dvo.id
                                                                INNER JOIN " . $this->cpanel . ".tbl_group AS gro ON gro.id=dvo.network_group 
                                                                LEFT JOIN " . $this->cpanel . ".customer_log as clo ON rao.callingstationid=clo.mac 
                                                                WHERE DATE_FORMAT(rao.acctstarttime,'%Y/%m/%d') >= '$startTime' AND DATE_FORMAT(rao.acctstarttime,'%Y/%m/%d') <= '$stopTime'
                                                                ".$sqlGroupo.$sqlDeviceo."
                                                            )*100 as pct_bw,
                    count(DISTINCT ra.callingstationid)/(
                                                                SELECT  count(DISTINCT rao.callingstationid) as abc
                                                                FROM " . $this->radius . ".radacct as rao
                                                                INNER JOIN " . $this->radius . ".`radcheck` as rco ON rao.username=rco.username
                                                                INNER JOIN " . $this->cpanel . ".tbl_device as dvo ON rco.id_device=dvo.id
                                                                INNER JOIN " . $this->cpanel . ".tbl_group AS gro ON gro.id=dvo.network_group 
                                                                LEFT JOIN " . $this->cpanel . ".customer_log as clo ON rao.callingstationid=clo.mac 
                                                                WHERE DATE_FORMAT(rao.acctstarttime,'%Y/%m/%d') >= '$startTime' AND DATE_FORMAT(rao.acctstarttime,'%Y/%m/%d') <= '$stopTime'
                                                                ".$sqlGroupo.$sqlDeviceo."
                                                            )*100 as pct_client
                                                          
                    FROM " . $this->radius . ".radacct as ra 
                    INNER JOIN " . $this->radius . ".radcheck as rc ON ra.username =  rc.username
                    INNER JOIN " . $this->cpanel . ".tbl_device as dv ON rc.id_device=dv.id
                    INNER JOIN " . $this->cpanel . ".tbl_group AS gr ON gr.id=dv.network_group
                    LEFT JOIN " . $this->cpanel . ".customer_log as cl ON ra.callingstationid=cl.mac 
                    WHERE DATE_FORMAT(ra.acctstarttime,'%Y/%m/%d') >= '$startTime' AND DATE_FORMAT(ra.acctstarttime,'%Y/%m/%d') <= '$stopTime'
                    ".$sqlGroup.$sqlDevice."
                    GROUP BY cl.osname ORDER BY client DESC";
        $topOs=$this->query($query)->result_object();
        foreach ($topOs as $value)
        {
            $value->total=$this->formatSize($value->total);
            $value->pct_bw=round($value->pct_bw,2);
            $value->pct_client=round($value->pct_client,2);
        }
        return $topOs;
    }
}